package com.hwg.emp.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hwg.emp.common.dto.KeysQueryDto;
import com.hwg.emp.common.util.DBUtil;
import com.hwg.emp.dao.EmployeeDAO;
import com.hwg.emp.dto.EmpDTO;
import com.hwg.emp.dto.PageBean;
import com.hwg.emp.model.DeptMD;
import com.hwg.emp.model.EmployeeMD;
import com.hwg.emp.model.UserMD;


public class EmployeeDAOImpl implements EmployeeDAO{
	@Override
	public EmpDTO selectOwnInfoById(String id) throws Exception {
		EmpDTO empDto=new EmpDTO();
		String sql="select * from emp_employee e,sys_user u where e.id=? and u.id=e.id";
		Connection con=DBUtil.getConnection();
		PreparedStatement ps=con.prepareStatement(sql);
		ps.setString(1, id);
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
			empDto.setId(rs.getString("e.id"));
			empDto.setAccount(rs.getString("account"));
			empDto.setName(rs.getString("name"));
			Integer sex=rs.getInt("sex");
			String ssex=null;
			DeptMD deptMd=new DeptMD();
			//deptMd.setName("人事部");
			deptMd.setId(rs.getString("dept_id"));
			if(sex==0){
				ssex="女"; 
			}else{
				ssex="男";
			}
			empDto.setSex(ssex);
			empDto.setAge(rs.getInt("age"));
			empDto.setBirth(rs.getDate("birth"));
			empDto.setPhone(rs.getString("phone"));
			empDto.setDept(deptMd);
		}
		return empDto;
	}
	
	@Override
	public int update(EmployeeMD t) throws Exception {
		String sql = "update emp_employee set sex=?,age=?,birth=?,phone=? where id=?";
		Connection con=DBUtil.getConnection();
		PreparedStatement ps=con.prepareStatement(sql);
		ps.setString(1, t.getSex());
		ps.setDate(3, (Date) t.getBirth());
		ps.setString(2, t.getAge());
		ps.setString(4, t.getPhone());
		ps.setString(5, t.getId());
		int i = ps.executeUpdate();
		return i;
		
	}
	@Override
	public EmployeeMD select(String uk, String ukValue) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("select * from emp_employee where ")
		.append(uk).append("=?"  );
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setObject(1, ukValue);
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
			EmployeeMD employee = new EmployeeMD();
			//UserMD user = new UserMD();
			employee.setId(rs.getString("id"));
			employee.setAge(rs.getString("age"));
			Integer sex=rs.getInt("sex");
			String ssex = null;
			if(sex==0){
				ssex="女"; 
			}else{
				ssex="男";
			}
			employee.setSex(ssex);
			employee.setBirth(rs.getDate("birth"));
			employee.setPhone(rs.getString("phone"));
			DeptMD dept = new DeptMD();
			dept.setId(rs.getString("dept_id"));
			employee.setDept(dept);
			return employee;
		}
		return null;
	}
	@Override
	public boolean exist(String uk, String ukValue) throws Exception {
		return false;
	}
	
	/**
	 * 增加员工
	 * @return 
	 */
	@Override
	public int insert(EmployeeMD t) throws Exception {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO emp_employee (id,sex,age,birth,phone,dept_id) VALUES (?,?,?,?,?,?)";
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, t.getId());
		ps.setString(2,t.getSex());
		ps.setString(3, t.getAge());
		ps.setDate(4, (Date) t.getBirth());
		ps.setString(5, t.getPhone());
		ps.setString(6, t.getDept().getId());
		int i =ps.executeUpdate();
		return i ;
	}

	@Override
	public List selectEmployees(String dept_id, PageBean pagebean) throws Exception {
		// TODO Auto-generated method stub
		List employees = new ArrayList();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT sys_user.account,sys_user.`name`,emp_employee.age,emp_employee.birth,emp_employee.id,emp_employee.phone,emp_employee.sex,emp_employee.dept_id FROM emp_employee,sys_user ")
		.append(" where emp_employee.id=sys_user.id and emp_employee.dept_id=? GROUP BY account" )
		.append(" limit ?,?");
		Connection con =DBUtil.getConnection();
		PreparedStatement ps = con.prepareStatement(sql.toString());
		ps.setString(1, dept_id);
		ps.setInt(2, pagebean.getStart());
		ps.setInt(3, pagebean.getPrecord());
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			EmployeeMD employee = new EmployeeMD();
			//UserMD user = new UserMD();
			employee.setId(rs.getString("id"));
			employee.setAccount(rs.getString("account"));
			employee.setName(rs.getString("name"));
			employee.setAge(rs.getString("age"));
			Integer sex=rs.getInt("sex");
			String ssex = null;
			if(sex==0){
				ssex="女"; 
			}else{
				ssex="男";
			}
			employee.setSex(ssex);
			employee.setBirth(rs.getDate("birth"));
			employee.setPhone(rs.getString("phone"));
			employees.add(employee);
			//employees.add(user);
		}		
		return employees;
	}

	@Override
	public int delete(String uk, String ukValue) throws Exception {
		Connection con=DBUtil.getConnection();
		StringBuilder sql=new StringBuilder();
		sql.append("delete  from emp_employee where ")
		.append(uk).append("=?");
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setObject(1, ukValue);
		return ps.executeUpdate();
	}

	@Override
	public String selectEmployeeDeptId(String userId) throws Exception {
		// TODO Auto-generated method stub
		String deptId=null;
		String sql="SELECT emp_employee.dept_id from sys_user,emp_employee WHERE sys_user.id=emp_employee.id AND sys_user.id=?";
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setString(1, userId);
		ResultSet rs=pst.executeQuery();
		if(rs.next()){
			deptId=rs.getString("dept_id");
		}
		return deptId;
	}

	@Override
	public int selectEmployeesAcount(String dept_id) throws Exception {
		// TODO Auto-generated method stub
		Connection con = DBUtil.getConnection();
		int e_count=0;
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT count(*) e_count FROM emp_employee ")
		.append(" where emp_employee.dept_id=?" );
		PreparedStatement ps = con.prepareStatement(sql.toString());
		ps.setString(1, dept_id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			e_count = rs.getInt("e_count");
		}
		return e_count;
	}

	@Override
	public List selectEmpsByCondition(String phone, PageBean pagebean) throws Exception {
		// TODO Auto-generated method stub
		List<EmployeeMD> employees = new ArrayList<EmployeeMD>();
		EmployeeMD employee = new EmployeeMD();
		Connection con = DBUtil.getConnection();
		StringBuilder sql = new StringBuilder();
		employee.setPhone(phone);
		sql.append(" from emp_employee where ");
		if (employee.getPhone()!=null) {
			sql.append(" phone like '%'?'%' ");
			PreparedStatement ps = con.prepareStatement("select emp_employee.age,emp_employee.birth,emp_employee.id,emp_employee.phone,emp_employee.sex,emp_employee.dept_id "+sql.toString());
			ps.setString(1, phone);
		    ResultSet rs = ps.executeQuery();
		    while(rs.next()){
		    	employee.setId(rs.getString("id"));
		    	employee.setPhone(rs.getString("phone"));
		    	employee.setSex(rs.getString("sex"));
		    	employee.setAge(rs.getString("age"));
		    	employee.setBirth(rs.getDate("birth"));
		    	employees.add(employee);
		    }
		}
		return employees;
	}

	@Override
	public int selectEmpsCountByCondition(String phone) throws Exception {
		// TODO Auto-generated method stub
		Connection con = DBUtil.getConnection();
		EmployeeMD employee = new EmployeeMD();
		int e_count=0;
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT count(*) e_count FROM emp_employee where ");
		employee.setPhone(phone);
		if (employee.getPhone()!=null) {
			sql.append("phone like '%'?'%'");
			PreparedStatement ps = con.prepareStatement(sql.toString());
			ps.setString(1, phone);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				e_count = rs.getInt("e_count");
			}
		}
		return e_count;
	}

	@Override
	public List<EmployeeMD> selectEmployee(String account, String name, String age, String phone, PageBean pagebean)
			throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List selectEmployees1(String dept_id, PageBean pagebean) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}
     
}
